#![allow(unused_variables)]
use sqlx::Error;
use super::CountNum;
use super::CustOrderBean;
use super::OrderItemBean;
use super::CustOrderQueryRequest;
use super::Table;
use sqlx::Row;

impl<'c> Table<'c, CustOrderBean> {
pub async fn activate_cust_order(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"update cs_cust_orders set active = true where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn add_cust_order(&self,cust_order:CustOrderBean) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"insert into cs_cust_orders ( name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id) values (?,?,?,?,?,?,?,?,?,?)"#
		)
		.bind(cust_order.name)
		.bind(cust_order.active)
		.bind(cust_order.total)
		.bind(cust_order.amount)
		.bind(cust_order.status_id)
		.bind(cust_order.pay_type_id)
		.bind(cust_order.phone)
		.bind(cust_order.address)
		.bind(cust_order.order_date_time)
		.bind(cust_order.user_id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn count_active_cust_order_records(&self) ->Result<CountNum, Error> {
	let result = sqlx::query_as(
	r#"select count(*) as countNum from cs_cust_orders where active = true"#
	)
	.fetch_one(&(&*self.pool).clone().unwrap())
	.await;
	return result;
}

pub async fn count_search_cust_orders_by_fields_records(&self,cust_order_query_request:CustOrderQueryRequest) ->Result<CountNum, Error> {
		let mut query = r#"select count(*) as countNum from cs_cust_orders where 1=1 "#.to_string();
		let name = cust_order_query_request.name.unwrap_or_default();
		if !name.is_empty() {
			let query_name  = format!("{}{}{}"," and name like '%",name,"%' ");
			query = format!("{} {}", query, query_name);
		}
		let active = cust_order_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let total = cust_order_query_request.total.unwrap_or_default();
		if !total.is_empty() {
			let query_total  = format!("{}{}{}"," and total = ",total," ");
			query = format!("{} {}", query, query_total);
		}
		let amount = cust_order_query_request.amount.unwrap_or_default();
		if !amount.is_empty() {
			let query_amount  = format!("{}{}{}"," and amount = ",amount," ");
			query = format!("{} {}", query, query_amount);
		}
		let status_id = cust_order_query_request.status_id.unwrap_or_default();
		if !status_id.is_empty() {
			let query_status_id  = format!("{}{}{}"," and status_id = ",status_id," ");
			query = format!("{} {}", query, query_status_id);
		}
		let pay_type_id = cust_order_query_request.pay_type_id.unwrap_or_default();
		if !pay_type_id.is_empty() {
			let query_pay_type_id  = format!("{}{}{}"," and pay_type_id = ",pay_type_id," ");
			query = format!("{} {}", query, query_pay_type_id);
		}
		let phone = cust_order_query_request.phone.unwrap_or_default();
		if !phone.is_empty() {
			let query_phone  = format!("{}{}{}"," and phone like '%",phone,"%' ");
			query = format!("{} {}", query, query_phone);
		}
		let address = cust_order_query_request.address.unwrap_or_default();
		if !address.is_empty() {
			let query_address  = format!("{}{}{}"," and address like '%",address,"%' ");
			query = format!("{} {}", query, query_address);
		}
		let order_date_time = cust_order_query_request.order_date_time.unwrap_or_default();
		if !order_date_time.is_empty() {
			let query_order_date_time  = format!("{}{}{}"," and date(order_date_time) = date('",order_date_time,"') ");
			query = format!("{} {}", query, query_order_date_time);
		}
		let user_id = cust_order_query_request.user_id.unwrap_or_default();
		if !user_id.is_empty() {
			let query_user_id  = format!("{}{}{}"," and user_id = ",user_id," ");
			query = format!("{} {}", query, query_user_id);
		}
		let result = sqlx::query_as(&query)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn delete_cust_order(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"delete from cs_cust_orders where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn find_cust_order_by_id(&self,id:i64) ->Result<CustOrderBean, Error> {
	let result = sqlx::query_as(
	r#"select id,name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id from cs_cust_orders where id = ? "#
		)
		.bind(id)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn find_cust_order_by_name(&self,name:String) ->Result<CustOrderBean, Error> {
	let result = sqlx::query_as(
	r#"select id,name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id from cs_cust_orders where name = ?;"#
		)
		.bind(name)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn list_active_cust_orders(&self) ->Result<Vec<CustOrderBean>, Error> {
		let result = sqlx::query_as(
	r#"select id,name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id from cs_cust_orders where active = true order by name asc"#
		)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn list_all_cust_orders(&self) ->Result<Vec<CustOrderBean>, Error> {
		let result = sqlx::query_as(
	r#"select id,name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id from cs_cust_orders"#
		)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn search_cust_orders_by_fields(&self,cust_order_query_request:CustOrderQueryRequest) ->Result<Vec<CustOrderBean>, Error> {
		let mut query = r#"select id,name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id from cs_cust_orders where 1=1 "#.to_string();
		let name = cust_order_query_request.name.unwrap_or_default();
		if !name.is_empty() {
			let query_name  = format!("{}{}{}"," and name like '%",name,"%' ");
			query = format!("{} {}", query, query_name);
		}
		let active = cust_order_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let total = cust_order_query_request.total.unwrap_or_default();
		if !total.is_empty() {
			let query_total  = format!("{}{}{}"," and total = ",total," ");
			query = format!("{} {}", query, query_total);
		}
		let amount = cust_order_query_request.amount.unwrap_or_default();
		if !amount.is_empty() {
			let query_amount  = format!("{}{}{}"," and amount = ",amount," ");
			query = format!("{} {}", query, query_amount);
		}
		let status_id = cust_order_query_request.status_id.unwrap_or_default();
		if !status_id.is_empty() {
			let query_status_id  = format!("{}{}{}"," and status_id = ",status_id," ");
			query = format!("{} {}", query, query_status_id);
		}
		let pay_type_id = cust_order_query_request.pay_type_id.unwrap_or_default();
		if !pay_type_id.is_empty() {
			let query_pay_type_id  = format!("{}{}{}"," and pay_type_id = ",pay_type_id," ");
			query = format!("{} {}", query, query_pay_type_id);
		}
		let phone = cust_order_query_request.phone.unwrap_or_default();
		if !phone.is_empty() {
			let query_phone  = format!("{}{}{}"," and phone like '%",phone,"%' ");
			query = format!("{} {}", query, query_phone);
		}
		let address = cust_order_query_request.address.unwrap_or_default();
		if !address.is_empty() {
			let query_address  = format!("{}{}{}"," and address like '%",address,"%' ");
			query = format!("{} {}", query, query_address);
		}
		let order_date_time = cust_order_query_request.order_date_time.unwrap_or_default();
		if !order_date_time.is_empty() {
			let query_order_date_time  = format!("{}{}{}"," and date(order_date_time) = date('",order_date_time,"') ");
			query = format!("{} {}", query, query_order_date_time);
		}
		let user_id = cust_order_query_request.user_id.unwrap_or_default();
		if !user_id.is_empty() {
			let query_user_id  = format!("{}{}{}"," and user_id = ",user_id," ");
			query = format!("{} {}", query, query_user_id);
		}
		let result = sqlx::query_as(&query)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn search_cust_orders_by_fields_by_page(&self,cust_order_query_request:CustOrderQueryRequest) ->Result<Vec<CustOrderBean>, Error> {
		let mut query = r#"select id,name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id from cs_cust_orders where 1=1 "#.to_string();
		let name = cust_order_query_request.name.unwrap_or_default();
		if !name.is_empty() {
			let query_name  = format!("{}{}{}"," and name like '%",name,"%' ");
			query = format!("{} {}", query, query_name);
		}
		let active = cust_order_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let total = cust_order_query_request.total.unwrap_or_default();
		if !total.is_empty() {
			let query_total  = format!("{}{}{}"," and total = ",total," ");
			query = format!("{} {}", query, query_total);
		}
		let amount = cust_order_query_request.amount.unwrap_or_default();
		if !amount.is_empty() {
			let query_amount  = format!("{}{}{}"," and amount = ",amount," ");
			query = format!("{} {}", query, query_amount);
		}
		let status_id = cust_order_query_request.status_id.unwrap_or_default();
		if !status_id.is_empty() {
			let query_status_id  = format!("{}{}{}"," and status_id = ",status_id," ");
			query = format!("{} {}", query, query_status_id);
		}
		let pay_type_id = cust_order_query_request.pay_type_id.unwrap_or_default();
		if !pay_type_id.is_empty() {
			let query_pay_type_id  = format!("{}{}{}"," and pay_type_id = ",pay_type_id," ");
			query = format!("{} {}", query, query_pay_type_id);
		}
		let phone = cust_order_query_request.phone.unwrap_or_default();
		if !phone.is_empty() {
			let query_phone  = format!("{}{}{}"," and phone like '%",phone,"%' ");
			query = format!("{} {}", query, query_phone);
		}
		let address = cust_order_query_request.address.unwrap_or_default();
		if !address.is_empty() {
			let query_address  = format!("{}{}{}"," and address like '%",address,"%' ");
			query = format!("{} {}", query, query_address);
		}
		let order_date_time = cust_order_query_request.order_date_time.unwrap_or_default();
		if !order_date_time.is_empty() {
			let query_order_date_time  = format!("{}{}{}"," and date(order_date_time) = date('",order_date_time,"') ");
			query = format!("{} {}", query, query_order_date_time);
		}
		let user_id = cust_order_query_request.user_id.unwrap_or_default();
		if !user_id.is_empty() {
			let query_user_id  = format!("{}{}{}"," and user_id = ",user_id," ");
			query = format!("{} {}", query, query_user_id);
		}
		let pagenum =  cust_order_query_request.page.unwrap_or_default();
		let pagesize =  cust_order_query_request.rows.unwrap_or_default();
		let start = (pagenum - 1) * pagesize;
		let limit = pagesize;
		query = format!("{} limit {} offset {} ", query, limit, start);
		let result = sqlx::query_as(&query)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn soft_delete_cust_order(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"update cs_cust_orders set active = false where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn toggle_cust_order(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"update cs_cust_orders set active = not active where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn update_cust_order(&self,cust_order:CustOrderBean) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"update cs_cust_orders set name = ? ,active = ? ,total = ? ,amount = ? ,status_id = ? ,pay_type_id = ? ,phone = ? ,address = ? ,order_date_time = ? ,user_id = ? where id = ? "#
		)
		.bind(cust_order.name)
		.bind(cust_order.active)
		.bind(cust_order.total)
		.bind(cust_order.amount)
		.bind(cust_order.status_id)
		.bind(cust_order.pay_type_id)
		.bind(cust_order.phone)
		.bind(cust_order.address)
		.bind(cust_order.order_date_time)
		.bind(cust_order.user_id)
		.bind(cust_order.id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn find_cust_order_indexed_name(&self,short_name:String) ->Result<Vec<CustOrderBean>, Error> {
	let result = sqlx::query_as(
	r#"select id,name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id from cs_cust_orders where name like concat(? ,'%') "#
	)
	.bind(short_name)
	.fetch_all(&(&*self.pool).clone().unwrap())
	.await;
	return result;
}

pub async fn select_order_list(&self,status_id:i64, pageNumber:i32, pageSize:i32) -> Result<Vec<CustOrderBean>, Error>{
   	let limit:i32 = pageSize;
    let start:i32 = (pageNumber-1)*pageSize;
    if status_id == 0 {
        let result = sqlx::query_as(
           	r#"select o.id,o.name,o.active,o.total,o.amount,o.status_id,o.pay_type_id,o.phone,o.address,o.order_date_time,o.user_id from cs_cust_orders o where o.active = true order by o.order_date_time desc limit ? offset ?"#
    	)
    	.bind(limit)
        .bind(start)
    	.fetch_all(&(&*self.pool).clone().unwrap())
    	.await;
    	return result;
    }else{
        let result = sqlx::query_as(
           	r#"select o.id,o.name,o.active,o.total,o.amount,o.status_id,o.pay_type_id,o.phone,o.address,o.order_date_time,o.user_id from cs_cust_orders o where o.status_id = ? and o.active = true order by o.order_date_time desc limit ? offset ?"#
    	)
        .bind(status_id)
    	.bind(limit)
        .bind(start)
    	.fetch_all(&(&*self.pool).clone().unwrap())
    	.await;
    	return result;
    }
}

pub async fn get_order_count(&self,status_id:i64) -> Result<CountNum, Error> {
    if status_id == 0 {
        let result = sqlx::query_as(
    		r#"select count(*) as countNum from cs_cust_orders where active = true"#
    	)
    	.fetch_one(&(&*self.pool).clone().unwrap())
    	.await;
    	return result;
    } else {
        let result = sqlx::query_as(
    		r#"select count(*) as countNum from cs_cust_orders where active = true and status_id = ?"#
    	)
        .bind(status_id)
    	.fetch_one(&(&*self.pool).clone().unwrap())
    	.await;
    	return result;
    }
}

pub async fn commit_cust_order(&self,cust_order:CustOrderBean,order_items:Vec<OrderItemBean>) -> Result<bool, Error> {
    let pool = &(&*self.pool).clone().unwrap();
    let mut transaction = pool.begin().await?;
    let result = sqlx::query("insert into cs_cust_orders ( name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id) values (?,?,?,?,?,?,?,?,?,?)")
        .bind(cust_order.name)
        .bind(cust_order.active)
        .bind(cust_order.total)
        .bind(cust_order.amount)
        .bind(cust_order.status_id)
        .bind(cust_order.pay_type_id)
        .bind(cust_order.phone)
        .bind(cust_order.address)
        .bind(cust_order.order_date_time)
        .bind(cust_order.user_id)
        .execute(&mut *transaction)
		.await;
	match result {
        Err(err) => {
            println!("rollback0");
            println!("{}", err);
       	    transaction.rollback().await?;
      		return Err(err);
       	},
		Ok(result_value) => {
			let cust_order_id = result_value.last_insert_id();
			println!("{}", cust_order_id);
			for order_item in order_items {
                let result3:CountNum = sqlx::query_as(
                   	    r#"select stock as countNum from cs_goodses where id = ? "#
              		)
              		.bind(order_item.goods_id)
              		.fetch_one(pool)
              		.await.unwrap();
                    println!("stock:{}", result3.count_num.clone());
                    if (result3.count_num.clone() as i32) < order_item.amount {
                        println!("rollback");
    				    transaction.rollback().await?;
       					return Ok(false);
                    }
                let result0 = sqlx::query(
                    r#"update cs_goodses set stock = stock - ? where id = ? "#
    				)
    				.bind(order_item.amount)
                    .bind(order_item.goods_id)
                    .execute(&mut *transaction)
    				.await.unwrap();
                let result2 = sqlx::query("insert into cs_order_items ( name,active,price,amount,goods_id,cust_order_id) values (?,?,?,?,?,?)")
                    .bind(order_item.name)
                    .bind(order_item.active)
                    .bind(order_item.price)
                    .bind(order_item.amount)
                    .bind(order_item.goods_id)
                    .bind(cust_order_id)
         			.execute(&mut *transaction)
         			.await.unwrap();

    		}
            println!("commit");
            transaction.commit().await?;
 			return Ok(true);
		}
	}
}

pub async fn list_my_cust_orders(&self, user_id:i64) ->Result<Vec<CustOrderBean>, Error> {
		let result = sqlx::query_as(
		  r#"select id,name,active,total,amount,status_id,pay_type_id,phone,address,order_date_time,user_id from cs_cust_orders where user_id = ? and active = true order by name asc"#
		)
		.bind(user_id)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}


pub async fn update_cust_order_status(&self,id:i64, status_id:i64) ->Result<u64, Error> {
	let result = sqlx::query(
	  r#"update cs_cust_orders set status_id = ?  where id = ? and active = true"#
	)
	.bind(status_id)
	.bind(id)
	.execute(&(&*self.pool).clone().unwrap())
	.await;
	match result {
		Ok(result) => {
			Ok(result.rows_affected())
		},
		Err(err) => {
			Err(err)
		}
	}
}

pub async fn delete_cust_order_with_order_items(&self,cust_order_id:i64) -> Result<bool, Error> {
    let pool = &(&*self.pool).clone().unwrap();
    let mut transaction = pool.begin().await?;
    let result = sqlx::query("delete from cs_order_items where cust_order_id = ?")
        .bind(cust_order_id)
        .execute(&mut *transaction)
		.await;
	match result {
        Err(err) => {
            println!("rollback0");
            println!("{}", err);
       	    transaction.rollback().await?;
      		return Err(err);
       	},
		Ok(result_value) => {
            _ = sqlx::query(
               	    r#"delete from cs_cust_orders where id = ? "#
          		)
          		.bind(cust_order_id)
          		.execute(&mut *transaction)
          		.await.unwrap();
            println!("commit");
            transaction.commit().await?;
 			return Ok(true);
		}
	}
}

}
